﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace 养老金支付系统
{
    public partial class FormGZBDC : FormBase
    {
        public FormGZBDC()
        {
            InitializeComponent();
        }

        string zfqh = string.Empty;//支付期号
        const string tjsql = @"select 支付期号,银行名称,养老金金额,养老金笔数,退休死亡金额,退休死亡笔数,
在职死亡金额,在职死亡笔数,缴费退款金额,缴费退款笔数,
金额小计,笔数小计 from 查拨款统计  where 支付期号=@ZFQH order by 银行编号";
        string bkny = string.Empty;//拨款年月
        string tbrq = string.Empty;//报表填报日期
        string filepath = PPS.OutputPath;
        FolderBrowserDialog fbd = new FolderBrowserDialog()
        {
            ShowNewFolderButton = true,
            Description = "请选择[报盘文件]要保存的位置..."
        };

        void buttonEnabled(bool b)
        {
            button1.Enabled = b;
            button2.Enabled = b;
            button3.Enabled = b;
        }

        void init()
        {
            this.Text = "送银行工资表导出";
            textBox1.Text = SqlHelper.getScalar("select dbo.GetNowQH()") as string;
            textBox2.Text = filepath;
            fbd.SelectedPath = filepath;
        }

        void find()
        {
            dataGridView1.DataSource = SqlHelper.getDataTable(tjsql, new SqlParameter("@ZFQH", this.zfqh));
            if (this.zfqh.Length == 6)
            {
                this.bkny = this.zfqh.Substring(0, 4) + "年" + this.zfqh.Substring(4, 2) + "月";
                DateTime rq = (DateTime)SqlHelper.getScalar("select 统计时间 from 支付统计 where 支付期号=@ZFQH", new SqlParameter("@ZFQH", this.zfqh));
                this.tbrq = rq.ToString("D");
            }
        }

        void SaveExcel(string yhmc ,string sql , string sumcol)
        {
            SqlParameter[] p1 = new SqlParameter[] { new SqlParameter("@ZFQH", this.zfqh), new SqlParameter("@ZHJY", true) };
            SqlParameter[] p2 = new SqlParameter[] { new SqlParameter("@ZFQH", this.zfqh), new SqlParameter("@ZHJY", false) };

            string path = string.Format("{0}\\{1}\\{2}\\", this.filepath, this.zfqh, yhmc);
            if (!Directory.Exists(path))//若文件夹不存在则新建文件夹   
            {
                Directory.CreateDirectory(path); //新建文件夹   
            }

            DataTable dt1 = SqlHelper.getDataTable(sql, p1);
            int bs1 = dt1.Rows.Count;
            double je1 = 0;
            if (bs1 > 0)
                je1 = double.Parse(dt1.Compute("sum(" + sumcol + ")", "true").ToString());
            string fileName1 = string.Format("{0}{1}{2}工资表-{3}元-{4}笔.xls", path, this.zfqh, yhmc, je1.ToString("0.00"), bs1.ToString());
            ExcelHelper.DataTableToExcel(dt1, fileName1);

            DataTable dt2 = SqlHelper.getDataTable(sql, p2);
            int bs2 = dt2.Rows.Count;
            double je2 = 0;
            if(bs2>0)
                je2 = double.Parse(dt2.Compute("sum(" + sumcol + ")", "true").ToString());
            string fileName2 = string.Format("{0}{1}{2}工资表-{3}元-{4}笔-[帐号验证不通过].xls", path, this.zfqh, yhmc, je2.ToString("0.00"), bs2.ToString());
            ExcelHelper.DataTableToExcel(dt2, fileName2);

            string msg = string.Format("第{0}期{1}工资表导出完成！\r\n其中帐号验证通过{2}元，{3}笔，不通过{4}元，{5}笔。",
                this.zfqh, yhmc, je1.ToString("0.00"), bs1.ToString(), je2.ToString("0.00"), bs2.ToString());
            MessageBox.Show(this, msg, "导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        void SaveText(string yhmc, string sql, string sumcol)
        {
            SqlParameter[] p1 = new SqlParameter[] { new SqlParameter("@ZFQH", this.zfqh), new SqlParameter("@ZHJY", true) };
            SqlParameter[] p2 = new SqlParameter[] { new SqlParameter("@ZFQH", this.zfqh), new SqlParameter("@ZHJY", false) };

            string path = string.Format("{0}\\{1}\\{2}\\", this.filepath, this.zfqh, yhmc);
            if (!Directory.Exists(path))//若文件夹不存在则新建文件夹   
            {
                Directory.CreateDirectory(path); //新建文件夹   
            }

            DataTable dt1 = SqlHelper.getDataTable(sql, p1);
            int bs1 = dt1.Rows.Count;
            double je1 = double.Parse(dt1.Compute("sum(支付金额)", "true").ToString());
            int pageSize = 3000;//TXT文件3000笔分批导出
            int pageCount1 = SqlHelper.getDataTablePageCount(dt1, pageSize);
            for (int i1 = 1; i1 <= pageCount1; i1++)
            {
                DataTable dtp1 = SqlHelper.getOnePageDataTable(dt1, i1, pageSize);
                int bs = dtp1.Rows.Count;
                double je = double.Parse(dtp1.Compute("sum(支付金额)", "true").ToString());
                string textFileName1 = string.Format("{0}{1}{2}工资表-{3}-{4}元-{5}笔.txt", path, this.zfqh, yhmc, i1.ToString(), je.ToString("0.00"), bs.ToString());
                TextHelper.ExportDataTableToTextFileNoTitle(dtp1, textFileName1);
            }

            DataTable dt2 = SqlHelper.getDataTable(sql, p2);
            int bs2 = dt2.Rows.Count;
            double je2 = 0;
            if (bs2 > 0)
                je2 = double.Parse(dt2.Compute("sum(" + sumcol + ")", "true").ToString());
            string fileName2 = string.Format("{0}{1}{2}工资表-{3}元-{4}笔-[帐号验证不通过].xls", path, this.zfqh, yhmc, je2.ToString("0.00"), bs2.ToString());
            ExcelHelper.DataTableToExcel(dt2, fileName2);

            string msg = string.Format("第{0}期{1}工资表导出完成！\r\n其中帐号验证通过{2}元，{3}笔，不通过{4}元，{5}笔。",
                this.zfqh, yhmc, je1.ToString("0.00"), bs1.ToString(), je2.ToString("0.00"), bs2.ToString());
            MessageBox.Show(this, msg, "导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        void output()
        {
            //工行工资表格式导出
            string ghsql = @"select
银行户名 as 姓名,
银行帐号 as 卡号,
case 银行编号 when 1 then 2 else 1 end as 行别,
case 银行编号 when 1 then '' else 银行行号 end as 跨行行号,
case 银行编号 when 1 then '' else '05101' end as 业务种类,
'' as 协议书号,
'' as 账号地址,
支付金额 as 应处理金额,
个人编号 as 备注,
'' as 实处理金额,
'' as 处理标志
from 查支付明细
where 支付期号=@ZFQH and 银行编号 in
(select 银行编号 from 支付银行 where 代发银行编号=1)
and dbo.CheckZH(银行帐号,银行编号)=@ZHJY 
order by 行别 desc,跨行行号,卡号";// and 支付类型码<>0
            SaveExcel("工商银行", ghsql, "应处理金额");
            //建行工资表格式导出
            string jhsql = @"select
个人编号 as 序号,
银行帐号 as 账号,
银行户名 as 户名,
支付金额 as 金额,
'' as 跨行标识,
'' as 行名,
'' as 银联行号,
支付期号+'工资' as 摘要,
个人编号 as 备注
from 支付明细
where 支付期号=@ZFQH 
and 银行编号 in (select 银行编号 from 支付银行 where 代发银行编号=2)
and dbo.CheckZH(银行帐号,银行编号)=@ZHJY 
order by 支付类型码,银行帐号";
            SaveExcel("建设银行", jhsql, "金额");// and 支付类型码<>0
            //农行本行网银代发格式
            /*
            string nhsql11 = @"select
个人编号,
银行户名 as 姓名,
银行帐号,
支付金额
from 查支付明细
where 支付期号=@ZFQH and 银行编号=0 and dbo.CheckZH(银行帐号)=@ZHJY and 支付类型码<>0
order by 银行帐号";
            SaveExcel("农业银行", nhsql11, "支付金额");
            string nhsql12 = @"select
个人编号,
银行帐号,
银行户名 as 姓名,
支付金额,
right(支付期号,2)+'月工资'
from 查支付明细
where 支付期号=@ZFQH and 银行编号=0 and dbo.CheckZH(银行帐号)=@ZHJY and 支付类型码<>0
order by 银行帐号";
            SaveText("农业银行", nhsql12, "支付金额");
             */
            string nhsql2 = @"select
个人编号,
银行帐号,
银行户名 as 姓名,
支付金额,
支付类型
from 查支付明细
where 支付期号=@ZFQH and 银行编号=0 and dbo.CheckZH(银行帐号,银行编号)=@ZHJY
order by 支付类型码,银行帐号";//and 支付类型码<>0
            SaveText("农业银行", nhsql2, "支付金额");

            string path = string.Format("{0}\\{1}\\", this.filepath, this.zfqh);
            if (!Directory.Exists(path))//若文件夹不存在则新建文件夹   
            {
                Directory.CreateDirectory(path); //新建文件夹   
            }
            DataTable bkdt = dataGridView1.DataSource as DataTable;
            if (bkdt != null)
            {
                string bkfilename = string.Format("{0}第{1}期拨款明细.xls", path, this.zfqh);
                ExcelHelper.DataTableToExcel(bkdt, bkfilename);
            }

        }

        void print()
        {

            FormPrintView fpv = new FormPrintView()
            {
                ReportDataSetName = "拨款统计",
                DataSources = dataGridView1.DataSource,
                ReportParam = new Microsoft.Reporting.WinForms.ReportParameter[] { new Microsoft.Reporting.WinForms.ReportParameter("bkny", this.bkny), new Microsoft.Reporting.WinForms.ReportParameter("tbrq",this.tbrq) 
                },
                ReportPath = "BKB.rdlc",
                WindowState = FormWindowState.Maximized
            };
            fpv.ShowDialog();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            buttonEnabled(false);
            if (fbd.ShowDialog() == DialogResult.OK)
            {
                string path = fbd.SelectedPath;
                if (path.Substring(path.Length - 1, 1) != @"\")
                    path = path + @"\";
                textBox2.Text = path;
                this.filepath = path;
                if (PPS.OutputPath != this.filepath)
                    PPS.OutputPath = path;
            }
            buttonEnabled(true);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            buttonEnabled(false);
            if (this.zfqh.Length == 6)
                find();
            buttonEnabled(true);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            buttonEnabled(false);
            if (this.filepath != string.Empty)
            {
                find();
                output();
            }
            buttonEnabled(true);
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            this.zfqh = textBox1.Text;
        }

        private void FormGZBDC_Load(object sender, EventArgs e)
        {
            init();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            print();
        }
    }
}
